package com.lanvideo.dao;

import com.lanvideo.entity.Playlist;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 播放列表数据访问层
 */
@Repository
public class PlaylistDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 创建播放列表
     */
    public int createPlaylist(Playlist playlist) {
        String sql = "INSERT INTO playlist (user_id, name, description, is_public) VALUES (?, ?, ?, ?)";
        return jdbcTemplate.update(sql, playlist.getUserId(), playlist.getName(), 
                playlist.getDescription(), playlist.getIsPublic());
    }

    /**
     * 更新播放列表
     */
    public int updatePlaylist(Playlist playlist) {
        String sql = "UPDATE playlist SET name = ?, description = ?, is_public = ?, updated_time = CURRENT_TIMESTAMP WHERE id = ?";
        return jdbcTemplate.update(sql, playlist.getName(), playlist.getDescription(), 
                playlist.getIsPublic(), playlist.getId());
    }

    /**
     * 删除播放列表
     */
    public int deletePlaylist(Integer id) {
        String sql = "DELETE FROM playlist WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID获取播放列表
     */
    public Playlist getPlaylistById(Integer id) {
        String sql = "SELECT p.*, u.name as user_name, " +
                "(SELECT COUNT(*) FROM playlist_video pv WHERE pv.playlist_id = p.id) as video_count " +
                "FROM playlist p " +
                "LEFT JOIN user u ON p.user_id = u.id " +
                "WHERE p.id = ?";
        List<Playlist> playlists = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Playlist.class), id);
        return playlists.isEmpty() ? null : playlists.get(0);
    }

    /**
     * 获取用户的播放列表
     */
    public List<Playlist> getUserPlaylists(Integer userId) {
        String sql = "SELECT p.*, u.name as user_name, " +
                "(SELECT COUNT(*) FROM playlist_video pv WHERE pv.playlist_id = p.id) as video_count " +
                "FROM playlist p " +
                "LEFT JOIN user u ON p.user_id = u.id " +
                "WHERE p.user_id = ? " +
                "ORDER BY p.updated_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Playlist.class), userId);
    }

    /**
     * 获取公开的播放列表
     */
    public List<Playlist> getPublicPlaylists() {
        String sql = "SELECT p.*, u.name as user_name, " +
                "(SELECT COUNT(*) FROM playlist_video pv WHERE pv.playlist_id = p.id) as video_count " +
                "FROM playlist p " +
                "LEFT JOIN user u ON p.user_id = u.id " +
                "WHERE p.is_public = TRUE " +
                "ORDER BY p.updated_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Playlist.class));
    }

    /**
     * 搜索播放列表
     */
    public List<Playlist> searchPlaylists(String keyword) {
        String sql = "SELECT p.*, u.name as user_name, " +
                "(SELECT COUNT(*) FROM playlist_video pv WHERE pv.playlist_id = p.id) as video_count " +
                "FROM playlist p " +
                "LEFT JOIN user u ON p.user_id = u.id " +
                "WHERE p.is_public = TRUE AND (p.name LIKE ? OR p.description LIKE ?) " +
                "ORDER BY p.updated_time DESC";
        String searchPattern = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Playlist.class), searchPattern, searchPattern);
    }

    /**
     * 检查播放列表是否属于用户
     */
    public boolean isPlaylistOwner(Integer playlistId, Integer userId) {
        String sql = "SELECT COUNT(*) FROM playlist WHERE id = ? AND user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, playlistId, userId);
        return count != null && count > 0;
    }

    /**
     * 获取播放列表数量
     */
    public int getUserPlaylistCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM playlist WHERE user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId);
        return count != null ? count : 0;
    }
}
